/*
 * Copyright 2017 Baiyyy
 * @(#)UploadService.java
 */

package com.byyy.ccts.modules.cc.rmi.impl;

import com.byyy.ccts.core.base.ILogBase;
import com.byyy.ccts.core.utils.PropertiesUtil;
import com.byyy.ccts.modules.cc.rmi.IUploadService;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 温度数据上传服务实现类
 * Created by DELL on 2018-11-19.
 */
public class UploadService extends UnicastRemoteObject implements IUploadService, ILogBase {

    private static final String SQL_INSERT_RECORD = "INSERT INTO RECORD(WZNAME, IPADDR, WDZ, SDZ, DT, KFSX, GPSJD, GPSWD, SJWDZ, SJSDZ) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String SQL_SELECT_REPEAT_RECORD = "SELECT WZName, DT FROM Record WHERE WZName = ? AND DT >= ? AND DT <= ? ORDER BY DT";

    private static final long serialVersionUID = 1L;

    private PropertiesUtil propertiesUtil = new PropertiesUtil();
    private String user = null;
    private String password = null;

    public UploadService() throws RemoteException {
        propertiesUtil.load(this.getClass().getClassLoader().getResourceAsStream("jeeweb.properties"));
        user = (String) propertiesUtil.get("upload.user");
        password = (String) propertiesUtil.get("upload.password");
        logger.info("ACCESS数据库用户名密码：" + user + ":" + password);
    }

    /**
     * 上传温度数据，提供两种方式上传数据
     * 1.Ucanaccess驱动
     *  Driver:net.ucanaccess.jdbc.UcanaccessDriver
     *  URL:jdbc:ucanaccess://F:\\main.mdb;jackcessOpener=com.byyy.ccts.core.utils.CryptCodecOpenerUtils
     *
     * 2.JDBC-ODBC驱动
     *  Driver:sun.jdbc.odbc.JdbcOdbcDriver
     *  URL:jdbc:odbc:TempUpload
     * 目前在使用第一种，如果存在问题，再修改为第二种方式，如果要修改为第二种方式，那么编译时请将版本指定为1.7
     *
     * @param records
     * @return
     * @throws RemoteException
     */
    @Override
    public int upload(List<Object[]> records) throws RemoteException {
        logger.debug("温度数据上传开始 长度{}", records.size());
        // 定义相关变量
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        String url = "jdbc:odbc:TempUpload";
        Connection conn = null;
        PreparedStatement preStat = null;
        ResultSet result = null;

        // 去重检查
        boolean repeatFlg = false;
        List<Object[]> repeatRecords = new ArrayList<>();
        List<Object[]> noRepeatRecords = new ArrayList<>();
        Timestamp startTime = new Timestamp(((java.util.Date) records.get(0)[4]).getTime());
        Timestamp endTime = new Timestamp(((java.util.Date) records.get(0)[4]).getTime());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        try {
            // 加载数据库
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            conn.setAutoCommit(false);
            // 获取开始时间以及结束时间
            for (Object[] objs : records) {
                if (startTime.after(new Timestamp(((java.util.Date) objs[4]).getTime()))) {
                    startTime = new Timestamp(((java.util.Date) objs[4]).getTime());
                }
                if (endTime.before(new Timestamp(((java.util.Date) objs[4]).getTime()))) {
                    endTime = new Timestamp(((java.util.Date) objs[4]).getTime());
                }
            }
            logger.info("设备{}时间范围{}至{},共{}条", (String) records.get(0)[0], sdf.format(startTime), sdf.format(endTime), records.size());

            // 根据保温箱ID以及时间查询重复数据
            preStat = conn.prepareStatement(SQL_SELECT_REPEAT_RECORD);
            preStat.setString(1, (String) records.get(0)[0]);
            preStat.setTimestamp(2, startTime);
            preStat.setTimestamp(3, endTime);
            result = preStat.executeQuery();
            while (result.next()) {
                repeatRecords.add(new Object[]{result.getString("WZName"), result.getTimestamp("DT")});
            }

            // 如果存在重复数据
            if (repeatRecords.size() > 0) {
                for (Object[] objs : records) {
                    repeatFlg = false;
                    for (Object[] repeatObjs : repeatRecords) {
                        if (((String) repeatObjs[0]).equals((String) objs[0])
                                && sdf.format(new Timestamp(((java.util.Date) repeatObjs[1]).getTime()))
                                    .equals(sdf.format(new Timestamp(((java.util.Date) objs[4]).getTime())))) {
                            repeatFlg = true;
                            break;
                        }
                    }
                    if (!repeatFlg) {
                        noRepeatRecords.add(objs);
                    }
                }

            }
            // 如果不存在重复单据
            else {
                noRepeatRecords = records;
            }
            if (noRepeatRecords .size() < 1) {
                logger.debug("温度数据上传完毕(数据重复上传，已拦截)");
                return 0;
            }
            // 执行SQL语句
            preStat = conn.prepareStatement(SQL_INSERT_RECORD);
            logger.debug("上传数据长度" + noRepeatRecords.size());
            for (Object[] objs : noRepeatRecords) {
                preStat.setString(1, (String) objs[0]);
                preStat.setString(2, (String) objs[1]);
                preStat.setFloat(3, (Float) objs[2]);
                preStat.setInt(4, (Integer) objs[3]);
                preStat.setTimestamp(5, new Timestamp(((java.util.Date) objs[4]).getTime()));
                preStat.setString(6, (String) objs[5]);
                preStat.setString(7, (String) objs[6]);
                preStat.setString(8, (String) objs[7]);
                preStat.setFloat(9, (Float) objs[8]);
                preStat.setInt(10, (Integer) objs[9]);
                preStat.executeUpdate();
            }
            conn.commit();

            // 关闭数据库相关链接等
            preStat.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                }
            }
            logger.error(e.getMessage(), e);
            throw new RemoteException(e.getMessage(), e);
        } finally {
            if (preStat != null) {
                try {
                    preStat.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        logger.debug("温度数据上传完毕 长度{}", noRepeatRecords.size());
        return 0;
    }
}
